<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>MySQL存储过程深度解析</title>
    <link href="https://cdn.staticfile.org/font-awesome/6.4.0/css/all.min.css" rel="stylesheet">
    <link href="https://cdn.staticfile.org/tailwindcss/2.2.19/tailwind.min.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Noto+Serif+SC:wght@400;500;600;700&family=NotoSans+SC:wght@300;400;500;700&display=swap" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/mermaid@latest/dist/mermaid.min.js"></script>
    <style>
        body {
            font-family: 'Noto Sans SC', Tahoma, Arial, Roboto, "Droid Sans", "Helvetica Neue", "Droid Sans Fallback", "Hiragino Sans GB", Simsun, sans-serif;
            color: #333;
            line-height: 1.6;
        }
        .hero {
            background: linear-gradient(135deg, #6e8efb 0%, #a777e3 100%);
        }
        h1, h2, h3, h4 {
            font-family: 'Noto Serif SC', serif;
            font-weight: 600;
        }
        .card {
            transition: all 0.3s ease;
            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
        }
        .card:hover {
            transform: translateY(-5px);
            box-shadow: 0 10px 20px rgba(0, 0, 0, 0.15);
        }
        .code-block {
            background: #282c34;
            border-radius: 8px;
            color: #abb2bf;
        }
        .pros-list li::before {
            content: "✓";
            color: #4CAF50;
            font-weight: bold;
            display: inline-block;
            width: 1em;
            margin-left: -1em;
        }
        .first-letter {
            float: left;
            font-size: 4.5rem;
            line-height: 0.65;
            margin: 0.1em 0.1em 0 0;
            color: #6e8efb;
            font-family: 'Noto Serif SC', serif;
            font-weight: 700;
        }
    </style>
</head>
<body class="bg-gray-50">
    <!-- Hero Section -->
    <section class="hero text-white py-20 px-4 md:px-0">
        <div class="container mx-auto max-w-4xl">
            <div class="flex flex-col items-center text-center">
                <span class="text-sm uppercase tracking-wider mb-4 bg-white bg-opacity-20 px-3 py-1 rounded-full">数据库开发指南</span>
                <h1 class="text-4xl md:text-5xl font-bold mb-6">MySQL存储过程深度解析</h1>
                <p class="text-xl md:text-2xl opacity-90 max-w-3xl leading-relaxed">
                    探索高效数据库操作的艺术 - 从基础概念到MyBatis实战应用
                </p>
                <div class="mt-10 flex space-x-4">
                    <a href="#definition" class="px-6 py-3 bg-white text-blue-600 font-medium rounded-lg hover:bg-gray-100 transition duration-300">
                        <i class="fas fa-book-open mr-2"></i>了解概念
                    </a>
                    <a href="#advantage" class="px-6 py-3 bg-transparent border-2 border-white text-white font-medium rounded-lg hover:bg-white hover:text-blue-600 transition duration-300">
                        <i class="fas fa-star mr-2"></i>优势解析
                    </a>
                </div>
            </div>
        </div>
    </section>

    <!-- Main Content -->
    <main class="container mx-auto max-w-4xl px-4 md:px-0 py-12">
        <!-- Definition Section -->
        <section id="definition" class="mb-16">
            <div class="flex items-center mb-8">
                <div class="w-12 h-12 rounded-full bg-blue-100 flex items-center justify-center mr-4">
                    <i class="fas fa-database text-blue-600 text-xl"></i>
                </div>
                <h2 class="text-3xl font-bold text-gray-800">存储过程是什么？</h2>
            </div>
            <div class="bg-white rounded-xl p-8 card">
                <div class="first-letter">存</div>
                <p class="text-lg text-gray-700 leading-relaxed">
                    储过程是能完成一定操作的一组SQL语句，它在创建后以被有权用户在任何需要的地方调用。通过使用过程，不仅可以简化客户端应用程序的开发和维护，而且可以提高应用程序的运行性能。(假如某应用程序需要经常向某张表中插入数据，并且在插入数据时需要对数据进行检查验证，为了简化客户端的维护，可以使用存储过程)
                </p>
                
                <!-- Visualization -->
                <div class="mt-10">
                    <div class="mermaid">
                        graph LR
                            A[客户端应用] -->|1. 调用| B(存储过程)
                            B -->|2. 执行SQL| C[数据库]
                            C -->|3. 返回结果| A
                            D[传统方式] -->|多次SQL调用| C
                    </div>
                    <p class="text-sm text-gray-500 mt-2 text-center">存储过程调用流程与传统方式对比</p>
                </div>
            </div>
        </section>

        <!-- Advantages Section -->
        <section id="advantage" class="mb-16">
            <div class="flex items-center mb-8">
                <div class="w-12 h-12 rounded-full bg-green-100 flex items-center justify-center mr-4">
                    <i class="fas fa-chart-line text-green-600 text-xl"></i>
                </div>
                <h2 class="text-3xl font-bold text-gray-800">存储过程的优势</h2>
            </div>
            
            <div class="grid md:grid-cols-3 gap-6">
                <div class="bg-white rounded-xl p-6 card">
                    <div class="w-10 h-10 rounded-full bg-blue-50 flex items-center justify-center mb-4">
                        <i class="fas fa-bolt text-blue-500"></i>
                    </div>
                    <h3 class="text-xl font-semibold mb-2 text-gray-800">性能优化</h3>
                    <p class="text-gray-600">储存过程是保存到MySQL中，只需要编译一次就可以了，调用的时候直接执行，避免了反复编译SQL语句的开销。</p>
                </div>
                
                <div class="bg-white rounded-xl p-6 card">
                    <div class="w-10 h-10 rounded-full bg-purple-50 flex items-center justify-center mb-4">
                        <i class="fas fa-code text-purple-500"></i>
                    </div>
                    <h3 class="text-xl font-semibold mb-2 text-gray-800">复杂逻辑封装</h3>
                    <p class="text-gray-600">一些复杂的操作，有业务逻辑的代码可以放在储存过程里面跑，这样性能会更高，同时也提高了代码的复用性。</p>
                </div>
                
                <div class="bg-white rounded-xl p-6 card">
                    <div class="w-10 h-10 rounded-full bg-orange-50 flex items-center justify-center mb-4">
                        <i class="fas fa-project-diagram text-orange-500"></i>
                    </div>
                    <h3 class="text-xl font-semibold mb-2 text-gray-800">减少网络开销</h3>
                    <p class="text-gray-600">SQL语句放在项目中，同一个Mapper方法调用10次，SQL需要编译10次，而存储过程只需一次编译多次执行。</p>
                </div>
            </div>
        </section>

        <!-- MyBatis Integration Section -->
        <section id="mybatis" class="mb-16">
            <div class="flex items-center mb-8">
                <div class="w-12 h-12 rounded-full bg-indigo-100 flex items-center justify-center mr-4">
                    <i class="fab fa-java text-indigo-600 text-xl"></i>
                </div>
                <h2 class="text-3xl font-bold text-gray-800">MyBatis调用存储过程</h2>
            </div>
            
            <div class="bg-white rounded-xl p-8 card">
                <p class="text-lg text-gray-700 mb-6 leading-relaxed">
                    MyBatis框架支持调用存储过程来执行数据库操作。存储过程是一组预编译的SQL语句集合，可以在数据库中进行存储和调用。使用MyBatis调用存储过程的步骤如下：
                </p>
                
                <ol class="list-decimal pl-6 space-y-4 text-gray-700 mb-8">
                    <li>在数据库中创建存储过程</li>
                    <li>在MyBatis的Mapper XML文件中定义调用存储过程的SQL语句</li>
                    <li>在Java代码中调用MyBatis的接口方法，执行存储过程</li>
                </ol>
                
                <h3 class="text-2xl font-semibold mb-4 text-gray-800 mt-10">示例代码</h3>
                
                <div class="mb-8">
                    <h4 class="text-lg font-medium mb-2 text-gray-700 flex items-center">
                        <i class="fas fa-database mr-2 text-blue-500"></i> 1. 创建存储过程
                    </h4>
                    <div class="code-block p-4 rounded-lg mt-2 overflow-x-auto">
                        <pre><code class="language-sql">CREATE PROCEDURE get_user_by_id(
    IN p_user_id INT,
    OUT p_user_name VARCHAR(255)
)
BEGIN
    SELECT user_name INTO p_user_name FROM user WHERE user_id = p_user_id;
END;</code></pre>
                    </div>
                </div>
                
                <div class="mb-8">
                    <h4 class="text-lg font-medium mb-2 text-gray-700 flex items-center">
                        <i class="fas fa-file-code mr-2 text-green-500"></i> 2. Mapper XML配置
                    </h4>
                    <div class="code-block p-4 rounded-lg mt-2 overflow-x-auto">
                        <pre><code class="language-xml">&lt;!-- 定义调用存储过程的SQL语句 -->
&lt;select id="getUserById" statementType="CALLABLE"&gt;
    {call get_user_by_id(#{userId, mode=IN, jdbcType=INTEGER}, #{userName, mode=OUT, jdbcType=VARCHAR})}
&lt;/select&gt;</code></pre>
                    </div>
                </div>
                
                <div>
                    <h4 class="text-lg font-medium mb-2 text-gray-700 flex items-center">
                        <i class="fas fa-laptop-code mr-2 text-purple-500"></i> 3. Java调用示例
                    </h4>
                    <div class="code-block p-4 rounded-lg mt-2 overflow-x-auto">
                        <pre><code class="language-java">public interface UserMapper {
    @Select("{call get_user_by_id(#{userId, mode=IN, jdbcType=INTEGER}, #{userName, mode=OUT, jdbcType=VARCHAR})}")
    void getUserById(@Param("userId") int userId, @Param("userName") String userName);
}

public class Main {
    public static void main(String[] args) {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            int userId = 123;
            String userName = null;
            userMapper.getUserById(userId, userName);
            System.out.println("User name: " + userName);
        } finally {
            sqlSession.close();
        }
    }
}</code></pre>
                    </div>
                </div>
                
                <!-- Process Flow Visualization -->
                <div class="mt-12">
                    <h4 class="text-xl font-semibold mb-4 text-gray-800 text-center">MyBatis调用存储过程流程</h4>
                    <div class="mermaid">
                        sequenceDiagram
                            participant Client as 客户端
                            participant MyBatis as MyBatis框架
                            participant DB as 数据库
                            
                            Client->>MyBatis: 调用getUserById方法
                            MyBatis->>DB: 执行存储过程调用
                            DB-->>MyBatis: 返回结果
                            MyBatis-->>Client: 返回映射结果
                    </div>
                </div>
            </div>
        </section>
        
        <!-- Best Practices Section -->
        <section class="mb-16">
            <div class="flex items-center mb-8">
                <div class="w-12 h-12 rounded-full bg-yellow-100 flex items-center justify-center mr-4">
                    <i class="fas fa-lightbulb text-yellow-500 text-xl"></i>
                </div>
                <h2 class="text-3xl font-bold text-gray-800">最佳实践建议</h2>
            </div>
            
            <div class="bg-white rounded-xl p-8 card">
                <div class="grid md:grid-cols-2 gap-8">
                    <div>
                        <h3 class="text-xl font-semibold mb-4 text-gray-800 flex items-center">
                            <span class="w-8 h-8 rounded-full bg-blue-100 text-blue-600 flex items-center justify-center mr-3">1</span>
                            适用场景
                        </h3>
                        <ul class="space-y-3 text-gray-600">
                            <li class="flex items-start">
                                <i class="fas fa-check-circle text-green-500 mr-2 mt-1"></i>
                                <span>需要批量处理大量数据的操作</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-check-circle text-green-500 mr-2 mt-1"></i>
                                <span>包含复杂业务逻辑的数据库操作</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-check-circle text-green-500 mr-2 mt-1"></i>
                                <span>需要高安全性的操作（如权限控制）</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-check-circle text-green-500 mr-2 mt-1"></i>
                                <span>频繁调用的SQL操作</span>
                            </li>
                        </ul>
                    </div>
                    
                    <div>
                        <h3 class="text-xl font-semibold mb-4 text-gray-800 flex items-center">
                            <span class="w-8 h-8 rounded-full bg-purple-100 text-purple-600 flex items-center justify-center mr-3">2</span>
                            注意事项
                        </h3>
                        <ul class="space-y-3 text-gray-600">
                            <li class="flex items-start">
                                <i class="fas fa-exclamation-triangle text-yellow-500 mr-2 mt-1"></i>
                                <span>避免过度使用导致业务逻辑分散</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-exclamation-triangle text-yellow-500 mr-2 mt-1"></i>
                                <span>考虑数据库兼容性问题</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-exclamation-triangle text-yellow-500 mr-2 mt-1"></i>
                                <span>注意错误处理和事务管理</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-exclamation-triangle text-yellow-500 mr-2 mt-1"></i>
                                <span>版本控制和文档化存储过程</span>
                            </li>
                        </ul>
                    </div>
                </div>
            </div>
        </section>
    </main>

    <!-- Footer -->
    <footer class="bg-gray-900 text-gray-300 py-10">
        <div class="container mx-auto max-w-4xl px-4 md:px-0">
            <div class="flex flex-col md:flex-row justify-between items-center">
                <div class="mb-4 md:mb-0">
                    <h3 class="text-xl font-semibold text-white mb-2">技术小馆</h3>
                    <p class="text-gray-400">专业的数据库技术资源中心</p>
                </div>
                <div>
                    <a href="http://www.yuque.com/jtostring" class="text-blue-400 hover:text-blue-300 transition duration-300 flex items-center">
                        <i class="fas fa-globe mr-2"></i> http://www.yuque.com/jtostring
                    </a>
                </div>
            </div>
            <div class="border-t border-gray-800 mt-8 pt-8 text-center text-sm text-gray-500">
                © 2023 技术小馆. 保留所有权利.
            </div>
        </div>
    </footer>

    <script>
        // Initialize Mermaid
        mermaid.initialize({
            startOnLoad: true,
            theme: 'default',
            flowchart: {
                useMaxWidth: true,
                htmlLabels: true,
                curve: 'basis'
            },
            sequence: {
                diagramMarginX: 50,
                diagramMarginY: 10,
                actorMargin: 50,
                width: 200,
                height: 100,
                boxMargin: 10,
                useMaxWidth: true
            }
        });
    </script>
</body>
</html>